package com.wkbb.common.utils;


import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.common.base.CharMatcher;
import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

import static org.apache.poi.ss.usermodel.CellType.*;


@Slf4j
public class ExcelUtil {

    /**
     * 方法名：setTitle
     * 功能：设置表头
     * 描述：
     * 创建人：typ
     * 创建时间：2018/10/19 10:20
     * 修改人：
     * 修改描述：
     * 修改时间：
     */
    private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] str) {
        try {
            HSSFRow row = sheet.createRow(0);
            //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
            for (int i = 0; i <= str.length; i++) {
                sheet.setColumnWidth(i, 15 * 256);
            }
            //设置为居中加粗,格式化时间格式
            HSSFCellStyle style = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setBold(true);
            style.setFont(font);
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setWrapText(true);
            style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
            style.setBorderTop(BorderStyle.THIN);
            style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
            //创建表头名称
            HSSFCell cell;
            for (int j = 0; j < str.length; j++) {
                cell = row.createCell(j);
                cell.setCellValue(str[j]);
                cell.setCellStyle(style);
            }
        } catch (Exception e) {
            log.info("导出时设置表头失败！");
            e.printStackTrace();
        }
    }

    /**
     * 方法名：setData
     * 功能：表格赋值
     * 描述：
     * 创建人：typ
     * 创建时间：2018/10/19 16:11
     * 修改人：
     * 修改描述：
     * 修改时间：
     */
    private static void setData(HSSFWorkbook workbook, HSSFSheet sheet, List<Object[]> data) {
        try {
            //设置为居中加粗,格式化时间格式
            HSSFCellStyle style = workbook.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setWrapText(true);
            HSSFCellStyle redStyle = workbook.createCellStyle();
            HSSFFont font=workbook.createFont();
            font.setColor(Font.COLOR_RED);
            redStyle.setFont(font);
            redStyle.setAlignment(HorizontalAlignment.CENTER);
            redStyle.setWrapText(true);


            setDataCell(sheet,style, redStyle, data);
            log.info("表格赋值成功！");
        } catch (Exception e) {
            log.info("表格赋值失败！");
            e.printStackTrace();
        }
    }

    private static void setDataCell(HSSFSheet sheet,HSSFCellStyle style, HSSFCellStyle redStyle, List<Object[]> data) {
        int rowNum = 1;
        String cellData;
        for (int i = 0; i < data.size(); i++) {
            HSSFRow row = sheet.createRow(rowNum);
            HSSFCell cell;
            for (int j = 0; j < data.get(i).length; j++) {
                cellData = data.get(i)[j].toString();
                cell = row.createCell(j);
                cell.setCellValue(cellData);
                cell.setCellStyle(getRedStyle(cellData)?redStyle:style);
            }
            rowNum++;
        }
    }

    private static void setDataCell(HSSFSheet sheet, List<Object[]> data) {
        int rowNum = 1;
        for (int i = 0; i < data.size(); i++) {
            HSSFRow row = sheet.createRow(rowNum);
            HSSFCell cell;
            for (int j = 0; j < data.get(i).length; j++) {
                cell = row.createCell(j);
                cell.setCellValue(data.get(i)[j].toString());
            }
            rowNum++;
        }
    }

    /**
     * 判断是否需要标红单元格
     * @param cellData 行数据
     * @return 判断条件
     */
    private static boolean getRedStyle(String cellData){
        List<String> redArray = Lists.newArrayList("体温异常", "低温", "高温");
        return redArray.indexOf(cellData) > -1;
    }

    private static void setData(HSSFSheet sheet, List<Object[]> data) {
        try {
            setDataCell(sheet, data);
            log.info("表格赋值成功！");
        } catch (Exception e) {
            log.info("表格赋值失败！");
            e.printStackTrace();
        }
    }

    /**
     * 方法名：setBrowser
     * 功能：使用浏览器下载
     * 描述：
     * 创建人：typ
     * 创建时间：2018/10/19 16:20
     * 修改人：
     * 修改描述：
     * 修改时间：
     */
    private static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String fileName) {
        try {
            //清空response
            response.reset();
            //设置response的Header
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
            OutputStream os = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/vnd.ms-excel;charset=gb2312");
            //将excel写入到输出流中
            workbook.write(os);
            os.flush();
            os.close();
            log.info("设置浏览器下载成功！");
        } catch (Exception e) {
            log.info("设置浏览器下载失败！");
            e.printStackTrace();
        }

    }


    /**
     * 方法名：importExcel
     * 功能：导入
     * 描述：
     * 创建人：typ
     * 创建时间：2018/10/19 11:45
     * 修改人：
     * 修改描述：
     * 修改时间：
     */
    public static List<Object[]> importExcel(String fileName) {
        try {
            InputStream inputStream = new FileInputStream(fileName);
            return importExcel(inputStream);
        } catch (Exception e) {
            log.info("导入文件解析失败！");
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 方法名：importExcel
     * 功能：导入
     * 描述：
     * 创建人：typ
     * 创建时间：2018/10/19 11:45
     * 修改人：
     * 修改描述：
     * 修改时间：
     */
    public static List<Object[]> importExcel(InputStream inputStream) {
        log.info("导入解析开始............");
        try {
            List<Object[]> list = new ArrayList<>();
            SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd");

            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            //获取sheet的行数
            int rows = sheet.getPhysicalNumberOfRows();
            int failNum = 0;//解析为空的数据行数，当同时有两行数据为空时，结束遍历。
            for (int i = 0; i < rows; i++) {
                //过滤表头行
                if (i == 0) {
                    continue;
                }
                //获取当前行的数据
                Row row = sheet.getRow(i);
                if(row == null ){
                    continue;
                }
                Object[] objects = new Object[row.getPhysicalNumberOfCells()];
                int index = 0;
                boolean isRowNull = true;
                for (Cell cell : row) {
                    if (cell.getCellType().equals(NUMERIC)) {
                        if(org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {// 判断单元格是否属于日期格式
                            java.util.Date date = cell.getDateCellValue();
                            String date1 = dff.format(date);   //日期转化
                            objects[index] = date1;
                            isRowNull = false;
                        }else{
                            //将数字转换成字符串
                            DecimalFormat df = new DecimalFormat("0");
                            objects[index] = df.format(cell.getNumericCellValue());
                            isRowNull = false;
                        }
                    }
                    if (cell.getCellType().equals(STRING)) {
                        //去除字符串首尾的全角,半角空格
                        objects[index] = CharMatcher.WHITESPACE.trimFrom(cell.getStringCellValue()).trim();
                        isRowNull = false;
                    }
                    if (cell.getCellType().equals(BOOLEAN)) {
                        objects[index] = cell.getBooleanCellValue();
                        isRowNull = false;
                    }
                    //空白值直接赋值为""
                    if(cell.getCellType().equals(BLANK)){
                        objects[index] = "";
                        isRowNull = false;
                    }
                    //公式
                    if(cell.getCellType().equals(FORMULA)){
                        objects[index] = cell.getCellFormula();
                        isRowNull = false;
                    }
                    //未知类型
                    if (cell.getCellType().equals(ERROR)) {
                        objects[index] = cell.getErrorCellValue();
                        isRowNull = false;
                    }
                    index++;
                }
                if(isRowNull){//整行数据都为空
                    failNum ++;
                }else {
                    failNum = 0;
                }
                if(failNum >10){
                    //解析为空的数据行数，当同时有10行数据为空时，结束遍历。
                    break;
                }
                list.add(objects);
            }
            log.info("导入文件解析成功！");
            return list;
        } catch (Exception e) {
            log.info("导入文件解析失败！");
            e.printStackTrace();
        }
        return null;
    }
    
    
    
    
    public static List<Object[]> dataExcel(Sheet sheet) {
        log.info("导入解析开始............");
        try {
            List<Object[]> list = new ArrayList<>();
            SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd");

            //获取sheet的行数
            int rows = sheet.getPhysicalNumberOfRows();
            int failNum = 0;//解析为空的数据行数，当同时有两行数据为空时，结束遍历。
            for (int i = 0; i < rows; i++) {
                //过滤表头行
                if (i == 0) {
                    continue;
                }
                //获取当前行的数据
                Row row = sheet.getRow(i);
                if(row == null ){
                    continue;
                }
                Object[] objects = new Object[row.getPhysicalNumberOfCells()];
                int index = 0;
                boolean isRowNull = true;
                for (Cell cell : row) {
                    if (cell.getCellType().equals(NUMERIC)) {
                        if(org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {// 判断单元格是否属于日期格式
                            java.util.Date date = cell.getDateCellValue();
                            String date1 = dff.format(date);   //日期转化
                            objects[index] = date1;
                            isRowNull = false;
                        }else{
                            //将数字转换成字符串
                            DecimalFormat df = new DecimalFormat("0.00");
                            objects[index] = df.format(cell.getNumericCellValue());
                            isRowNull = false;
                        }
                    }
                    if (cell.getCellType().equals(STRING)) {
                        //去除字符串首尾的全角,半角空格
                        objects[index] = CharMatcher.WHITESPACE.trimFrom(cell.getStringCellValue()).trim();
                        isRowNull = false;
                    }
                    if (cell.getCellType().equals(BOOLEAN)) {
                        objects[index] = cell.getBooleanCellValue();
                        isRowNull = false;
                    }
                    //空白值直接赋值为""
                    if(cell.getCellType().equals(BLANK)){
                        objects[index] = "";
                        isRowNull = false;
                    }
                    //公式
                    if(cell.getCellType().equals(FORMULA)){
                        objects[index] = cell.getCellFormula();
                        isRowNull = false;
                    }
                    //未知类型
                    if (cell.getCellType().equals(ERROR)) {
                        objects[index] = cell.getErrorCellValue();
                        isRowNull = false;
                    }
                    index++;
                }
                if(isRowNull){//整行数据都为空
                    failNum ++;
                }else {
                    failNum = 0;
                }
                if(failNum >10){
                    //解析为空的数据行数，当同时有10行数据为空时，结束遍历。
                    break;
                }
                list.add(objects);
            }
            log.info("导入文件解析成功！");
            return list;
        } catch (Exception e) {
            log.info("导入文件解析失败！");
            e.printStackTrace();
        }
        return null;
    }

    public static String NO_DEFINE = "no_define";//未定义的字段
    public static String DEFAULT_DATE_PATTERN="yyyy年MM月dd日";//默认日期格式
    public static int DEFAULT_COLOUMN_WIDTH = 17;
    /*
     * 导出Excel 97(.xls)格式 ，少量数据
     * @param title 标题行
     * @param headMap 属性-列名
     * @param jsonArray 数据集
     * @param datePattern 日期格式，null则用默认日期格式
     * @param colWidth 列宽 默认 至少17个字节
     * @param out 输出流
    public static void exportExcel(String title,Map<String, String> headMap,JSONArray jsonArray,String datePattern,int colWidth, OutputStream out) {
        if(datePattern==null) datePattern = DEFAULT_DATE_PATTERN;
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        workbook.createInformationProperties();
        workbook.getDocumentSummaryInformation().setCompany("*****公司");
        SummaryInformation si = workbook.getSummaryInformation();
        si.setAuthor("JACK");  //填加xls文件作者信息
        si.setApplicationName("导出程序"); //填加xls文件创建程序信息
        si.setLastAuthor("最后保存者信息"); //填加xls文件最后保存者信息
        si.setComments("JACK is a programmer!"); //填加xls文件作者信息
        si.setTitle("POI导出Excel"); //填加xls文件标题信息
        si.setSubject("POI导出Excel");//填加文件主题信息
        si.setCreateDateTime(new Date());
        //表头样式
        HSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 20);
        titleFont.setBoldweight((short) 700);
        titleStyle.setFont(titleFont);
        // 列头样式
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerStyle.setFont(headerFont);
        // 单元格样式
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        HSSFFont cellFont = workbook.createFont();
        cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        cellStyle.setFont(cellFont);
        // 生成一个(带标题)表格
        HSSFSheet sheet = workbook.createSheet();
        // 声明一个画图的顶级管理器
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        // 定义注释的大小和位置,详见文档
        HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
                0, 0, 0, (short) 4, 2, (short) 6, 5));
        // 设置注释内容
        comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
        // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
        comment.setAuthor("JACK");
        //设置列宽
        int minBytes = colWidth<DEFAULT_COLOUMN_WIDTH?DEFAULT_COLOUMN_WIDTH:colWidth;//至少字节数
        int[] arrColWidth = new int[headMap.size()];
        // 产生表格标题行,以及设置列宽
        String[] properties = new String[headMap.size()];
        String[] headers = new String[headMap.size()];
        int ii = 0;
        for (Iterator<String> iter = headMap.keySet().iterator(); iter
                .hasNext();) {
            String fieldName = iter.next();

            properties[ii] = fieldName;
            headers[ii] = fieldName;

            int bytes = fieldName.getBytes().length;
            arrColWidth[ii] =  bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii,arrColWidth[ii]*256);
            ii++;
        }
        // 遍历集合数据，产生数据行
        int rowIndex = 0;
        for (Object obj : jsonArray) {
            if(rowIndex == 65535 || rowIndex == 0){
                if ( rowIndex != 0 ) sheet = workbook.createSheet();//如果数据超过了，则在第二页显示

                HSSFRow titleRow = sheet.createRow(0);//表头 rowIndex=0
                titleRow.createCell(0).setCellValue(title);
                titleRow.getCell(0).setCellStyle(titleStyle);
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));

                HSSFRow headerRow = sheet.createRow(1); //列头 rowIndex =1
                for(int i=0;i<headers.length;i++)
                {
                    headerRow.createCell(i).setCellValue(headers[i]);
                    headerRow.getCell(i).setCellStyle(headerStyle);

                }
                rowIndex = 2;//数据内容从 rowIndex=2开始
            }
            JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
            HSSFRow dataRow = sheet.createRow(rowIndex);
            for (int i = 0; i < properties.length; i++)
            {
                HSSFCell newCell = dataRow.createCell(i);

                Object o =  jo.get(properties[i]);
                String cellValue = "";
                if(o==null) cellValue = "";
                else if(o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);
                else cellValue = o.toString();

                newCell.setCellValue(cellValue);
                newCell.setCellStyle(cellStyle);
            }
            rowIndex++;
        }
        // 自动调整宽度
        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }
        try {
            workbook.write(out);
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }*/

    public static void exportExcel(String title, String[] headers, List<Object[]> data,HttpServletResponse response){
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        //建立新的sheet对象（excel的表单）
        HSSFSheet sheet = workbook.createSheet(title);
        //表头样式
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        Font titleFont = workbook.createFont();
        titleFont.setBold(true);
        titleStyle.setFont(titleFont);
        // 单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        setTitle(workbook,sheet,headers);
        setData(sheet,data);
        //下载
        // 清空response
        response.reset();
        try {
            String fileType = "xls";
            String fileName="我报表";
            final SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMddHH24mmssSSS");
            final String formatStr = formatter.format(new Date());
            fileName = fileName + formatStr + "." + fileType;
            System.out.println(fileName);

            //导出到浏览器下载
            Date date =new Date();
            SimpleDateFormat sdf =new SimpleDateFormat("yyyyMMddHHmmss");
            String time =sdf.format(date);

            // 响应到客户端
            downResponse(response, workbook);
            //response.setHeader("Content-Disposition", "attachment;filename="+time +".xls");
            //response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            //response.setContentType("application/vnd.ms-excel;charset=utf-8");
            //response.setHeader("Content-disposition", "attachment;filename="+new String(title.getBytes(),"iso-8859-1")+".xls");
            //定义下载的类型，标明是excel文件
            //response.setContentType("application/vnd.ms-excel");
            //response.setHeader("Pragma", "no-cache");
            //response.setHeader("Cache-Control", "no-cache");
            //response.setDateHeader("Expires", 0);

            /*OutputStream output;
            output = response.getOutputStream();
            //清空缓存
            response.reset();
            //定义浏览器响应表头，顺带定义下载名，比如students(中文名需要转义)
            response.setHeader("Content-disposition", "attachment;filename="+new String("工作网格员".getBytes(),"UTF-8")+".xls");
            //定义下载的类型，标明是excel文件
            response.setContentType("application/vnd.ms-excel");
            //这时候把创建好的excel写入到输出流
            workbook.write(output);
            //养成好习惯，出门记得随手关门
            output.close();*/

            //导出到本地
            /*File file = new File("C://"+title+time+".xls");
            FileOutputStream fileOutputStream = new FileOutputStream(file);
            workbook.write(fileOutputStream);
            fileOutputStream.close();
            workbook.close();*/

        } catch (Exception e) {
            e.printStackTrace();
        }
    }



    public static void exportAttendExcel(String title, String[] headers, List<Object[]> data,HttpServletResponse response){
        // 声明一个工作薄
        HSSFWorkbook workbook = createWorkBook(title, headers, data);

        // 清空response
        response.reset();
        //导出到浏览器下载
        downResponse(response, workbook);

    }

    public static void exportAttendExcel(String sheetName,String fileName, String[] headers, List<Object[]> data,HttpServletResponse response){
        HSSFWorkbook workbook = createWorkBook(sheetName, headers, data);
        // 清空response
        response.reset();
        //导出到浏览器下载
        downResponse(response, fileName, workbook);
    }

    private static HSSFWorkbook createWorkBook(String sheetName, String[] headers, List<Object[]> data) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        //建立新的sheet对象（excel的表单）
        HSSFSheet sheet = workbook.createSheet(sheetName);
        setTitle(workbook, sheet, headers);
        setData(workbook, sheet, data);
        return workbook;
    }

    private static void downResponse(HttpServletResponse response, HSSFWorkbook workbook) {
        // 响应到客户端
        try {
            response.reset();// 清除首部的空白行
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            response.setDateHeader("Expires", 0);
            response.flushBuffer();
            final OutputStream os = response.getOutputStream();
            workbook.write(os);
            os.flush();
            os.close();
        } catch (final Exception e) {
            e.printStackTrace();
        }
    }

    private static void downResponse(HttpServletResponse response,String fileName, HSSFWorkbook workbook) {
        // 响应到客户端
        try {
            response.reset();// 清除首部的空白行
            response.setCharacterEncoding("UTF-8");
//            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setContentType("application/octet-stream");
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            response.setDateHeader("Expires", 0);
            response.addHeader("Content-Disposition", "attachment; filename=\"" +
                    new String((fileName + ".xls").getBytes("UTF-8"), "ISO-8859-1") + "\";filename*=UTF-8''"+
                    new String((fileName + ".xls").getBytes("UTF-8"), "ISO-8859-1"));
            response.flushBuffer();
            final OutputStream os = response.getOutputStream();
            workbook.write(os);
            os.flush();
            os.close();
        } catch (final Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出Excel 2007 OOXML (.xlsx)格式
     * @param title 标题行
     * @param headMap 属性-列头
     * @param jsonArray 数据集
     * @param datePattern 日期格式，传null值则默认 年月日
     * @param colWidth 列宽 默认 至少17个字节
     * @param out 输出流
     */
    public static void exportExcelX(String title, Map<String, String> headMap, JSONArray jsonArray, String datePattern, int colWidth, OutputStream out) {
        if(datePattern==null) datePattern = DEFAULT_DATE_PATTERN;
        // 声明一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        //建立新的sheet对象（excel的表单）
        XSSFSheet sheet = workbook.createSheet(title);
        //表头样式
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        Font titleFont = workbook.createFont();
        titleFont.setBold(true);
        titleStyle.setFont(titleFont);
        // 单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 生成一个(带标题)表格
        //SXSSFSheet sheet = workbook.createSheet();
        //设置列宽
        int minBytes = colWidth<DEFAULT_COLOUMN_WIDTH?DEFAULT_COLOUMN_WIDTH:colWidth;//至少字节数
        int[] arrColWidth = new int[headMap.size()];
        // 产生表格标题行,以及设置列宽
        String[] properties = new String[headMap.size()];
        String[] headers = new String[headMap.size()];
        int ii = 0;
        for (Iterator<String> iter = headMap.keySet().iterator(); iter.hasNext();) {
            String fieldName = iter.next();
            properties[ii] = fieldName;
            headers[ii] = headMap.get(fieldName);
            int bytes = fieldName.getBytes().length;
            arrColWidth[ii] =  bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii,arrColWidth[ii]*256);
            ii++;
        }
        // 遍历集合数据，产生数据行
        int rowIndex = 1;
        for (Object obj : jsonArray) {
            if(rowIndex == 65535 || rowIndex == 1){
                if ( rowIndex != 1 ) sheet = workbook.createSheet();//如果数据超过了，则在第二页显示

                //XSSFRow titleRow = sheet.createRow(0);//表头 rowIndex=0
                //titleRow.createCell(0).setCellValue(title);
                //titleRow.getCell(0).setCellStyle(titleStyle);
                //sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));

                XSSFRow headerRow = sheet.createRow(0); //列头 rowIndex =1
                for(int i=0;i<headers.length;i++)
                {
                    headerRow.createCell(i).setCellValue(headers[i]);
                    //headerRow.getCell(i).setCellStyle(headerStyle);
                }
                rowIndex = 1;//数据内容从 rowIndex=2开始
            }
            JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
            XSSFRow dataRow = sheet.createRow(rowIndex);
            for (int i = 0; i < properties.length; i++)
            {
                XSSFCell newCell = dataRow.createCell(i);

                Object o =  jo.get(properties[i]);
                String cellValue = "";
                if (o == null) {
                    cellValue = "";
                } else if (o instanceof java.util.Date) {
                    cellValue = new SimpleDateFormat(datePattern).format(o);
                } else if (o instanceof Float || o instanceof Double) {
                    cellValue = new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
                } else {
                    cellValue = o.toString();
                }
                newCell.setCellValue(cellValue);
                //newCell.setCellStyle(cellStyle);
            }
            rowIndex++;
        }
        // 自动调整宽度
        /*for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }*/
        try {
            workbook.write(out);
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出Excel 2007 OOXML (.xlsx)格式
     * @param title 标题行
     * @param headMap 属性-列头
     * @param jsonArray 数据集
     * @param datePattern 日期格式，传null值则默认 年月日
     * @param colWidth 列宽 默认 至少17个字节
     * @param out 输出流
     */
    public static void exportExcelXTemp(String title, Map<String, String> headMap, JSONArray jsonArray, String datePattern, int colWidth, OutputStream out) {
        if(datePattern==null) datePattern = DEFAULT_DATE_PATTERN;
        // 声明一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        //建立新的sheet对象（excel的表单）
        XSSFSheet sheet = workbook.createSheet("全校教职工表");
        //表头样式
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        Font titleFont = workbook.createFont();
        titleFont.setBold(true);
        titleStyle.setFont(titleFont);
        // 列头样式
        //CellStyle headerStyle = workbook.createCellStyle();
        //headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        /*Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setBoldweight();
        headerStyle.setFont(headerFont);*/
        // 单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        /*cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setBorderBottom(HSSFCellStyle.THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.THIN);
        cellStyle.setBorderRight(HSSFCellStyle.THIN);
        cellStyle.setBorderTop(HSSFCellStyle.THIN);
        cellStyle.setAlignment(HSSFCellStyle.CENTER);*/
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 生成一个(带标题)表格
        //SXSSFSheet sheet = workbook.createSheet();
        //设置列宽
        int minBytes = colWidth<DEFAULT_COLOUMN_WIDTH?DEFAULT_COLOUMN_WIDTH:colWidth;//至少字节数
        int[] arrColWidth = new int[headMap.size()];
        // 产生表格标题行,以及设置列宽
        String[] properties = new String[headMap.size()];
        String[] headers = new String[headMap.size()];
        int ii = 0;
        for (Iterator<String> iter = headMap.keySet().iterator(); iter.hasNext();) {
            String fieldName = iter.next();
            properties[ii] = fieldName;
            headers[ii] = headMap.get(fieldName);
            int bytes = fieldName.getBytes().length;
            arrColWidth[ii] =  bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii,arrColWidth[ii]*256);
            ii++;
        }
        // 遍历集合数据，产生数据行
        int rowIndex = 1;
        for (Object obj : jsonArray) {
            if(rowIndex == 65535 || rowIndex == 1){
                if ( rowIndex != 1 ) sheet = workbook.createSheet();//如果数据超过了，则在第二页显示

                //XSSFRow titleRow = sheet.createRow(0);//表头 rowIndex=0
                //titleRow.createCell(0).setCellValue(title);
                //titleRow.getCell(0).setCellStyle(titleStyle);
                //sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));

                XSSFRow headerRow = sheet.createRow(0); //列头 rowIndex =1
                for(int i=0;i<headers.length;i++)
                {
                    headerRow.createCell(i).setCellValue(headers[i]);
                    //headerRow.getCell(i).setCellStyle(headerStyle);
                }
                rowIndex = 1;//数据内容从 rowIndex=2开始
            }
            JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
            XSSFRow dataRow = sheet.createRow(rowIndex);
            for (int i = 0; i < properties.length; i++)
            {
                XSSFCell newCell = dataRow.createCell(i);

                Object o =  jo.get(properties[i]);
                String cellValue = "";
                if (o == null) {
                    cellValue = "";
                } else if (o instanceof java.util.Date) {
                    cellValue = new SimpleDateFormat(datePattern).format(o);
                } else if (o instanceof Float || o instanceof Double) {
                    cellValue = new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
                } else {
                    cellValue = o.toString();
                }
                newCell.setCellValue(cellValue);
                //newCell.setCellStyle(cellStyle);
            }
            rowIndex++;
        }
        // 自动调整宽度
        /*for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }*/
        try {
            workbook.write(out);
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    //Web 导出excel
    public static void downloadExcelFile(String title, Map<String,String> headMap, JSONArray ja, HttpServletResponse response){
        try {
            /*ByteArrayOutputStream os = new ByteArrayOutputStream();
            ExcelUtil.exportExcelX(title,headMap,ja,"yyyy-MM-dd",0,os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();*/

            //response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            //response.setHeader("Content-Disposition", "attachment;filename="+ new String((title + ".xlsx").getBytes(), "iso-8859-1"));
            //response.setContentType("application/vnd.ms-excel;charset=utf-8");
            //response.setHeader("Content-Disposition","attachment;filename="+StringUtil.toUtf8String("全校教职工基本信息一览表.xls"));
            //设置response的Header
            //response.setCharacterEncoding("utf-8");
            //response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            //response.addHeader("Content-Disposition", "attachment;filename=fileName"+".xlsx");

            //response.setContentType("application/vnd.ms-excel");
            //response.addHeader("Content-Disposition", "attachment;filename=fileName"+".xls");

            //response.setContentLength(content.length);

            /*ServletOutputStream outputStream = response.getOutputStream();
            BufferedInputStream bis = new BufferedInputStream(is);
            BufferedOutputStream bos = new BufferedOutputStream(outputStream);
            byte[] buff = new byte[8192];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
            bis.close();
            bos.close();
            outputStream.flush();
            outputStream.close();*/

            OutputStream outXlsx = new FileOutputStream("E://b.xlsx");
            System.out.println("正在导出xlsx....");
            java.util.Date d2 = new java.util.Date();
            ExcelUtil.exportExcelX(title,headMap,ja,"yyyy-MM-dd",0,outXlsx);
            System.out.println("共"+ja.size()+"条数据,执行"+(new Date().getTime()-d2.getTime())+"ms");
            outXlsx.close();

        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    //测试导入
    public static void main(String[] args) {
        try {
            String fileName = "E:/b.xlsx";
            File file = new File(fileName);
            List<Object[]> list = importExcel(new FileInputStream(file));
            for (int i = 0; i < list.size(); i++) {
                log.info(JSONObject.toJSONString(list.get(i)));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}